﻿using System;
using System.Linq;
using System.Linq.Expressions;
using System.Web.UI;
using SBACN.WebSite.Code;
using SBACN.WebSite.Models;
using SBACN.WebSite.Common;
using System.Data.OleDb;
using System.IO;
using System.Collections.Generic;
using System.Data;

namespace SBACN.WebSite.Admin.MemberService.Education
{
    public partial class ExamSubjectAdd : BasePage
    {
		/// <summary>
		/// 系统字典类型
		/// </summary>
		public string SystemType
		{
			get
			{
				return this.SystemCode;
			}
		}

        protected void Page_Load(object sender, EventArgs e)
        {
			if(!Page.IsPostBack)
			{
				if (!AdminBasePage.CheckCodeRight(AdminBasePage.AdminInfo, SystemType))
				{
					AdminBasePage.RedirectWithMessage(AdminDefaultPage, "您没有访问当前页面的权限,请与系统管理员联系!");
					return;
				}

				var ID = Request.QueryString["id"];

				if (ID == null)
				{
					this.EditSubbtn.Visible = false;
				}
				else
				{
					int id = Convert.ToInt32(ID);
					this.HidSubjectId.Value = id.ToString();
					if (!Page.IsPostBack)
					{
						EditExamSubject(id);
					}
					this.Subbtn.Visible = false;
				}
				this.Member.Attributes.Add("onpaste", "ClearMemberID();");
			}
        }

        protected void Subbtn_Click(object sender, EventArgs e)
        {
            TB_Examination NewObjExam = DAL.DALHelper.Instance.Create<TB_Examination>();
            NewObjExam.Subject = this.Subject.Text.ToString();
            NewObjExam.StartDate = Convert.ToDateTime(this.StartDate.Text);
            NewObjExam.EndDate = Convert.ToDateTime(this.EndDate.Text + " 23:59:59");
            NewObjExam.Deadline = Convert.ToDateTime(this.ExamDate.Text);
			NewObjExam.CreateBy = AdminBasePage.AdminInfo.UserName;
            NewObjExam.CreateDate = DateTime.Now;
            DAL.DALHelper.Instance.Add<TB_Examination>(NewObjExam);
            DAL.DALHelper.Instance.SaveChanges();

            int ExamID = NewObjExam.ID;

            // 单位限额
            string CompanyStr = this.StrCompany.Value;
            string[] Company = CompanyStr.Split('⊙');
            foreach (string C in Company)
            {
                if (C != "")
                {
                    string[] cnt = C.Split('-');
                    TB_ExamQuota NewObjCQ = DAL.DALHelper.Instance.Create<TB_ExamQuota>();
                    NewObjCQ.ExamID = ExamID;
                    NewObjCQ.MemberID = Convert.ToInt32(cnt[0]);
                    NewObjCQ.Count = Convert.ToInt32(cnt[1]);
                    DAL.DALHelper.Instance.Add<TB_ExamQuota>(NewObjCQ);
                }
            }

			DAL.DALHelper.Instance.SaveChanges();

			bool hasError = false;
			if (!string.IsNullOrWhiteSpace(file_Excel.FileName))
			{
				try
				{
					string Url = BasePage.UploadFile(SystemCode, this.file_Excel.FileName, this.file_Excel.FileBytes);
					string sheel = "Sheet1";
					var uploadExcel = ExcelAccess.GetExcelRows(Server.MapPath(Url), sheel);
					if (uploadExcel.Tables[0].Columns.Count < 2 || !uploadExcel.Tables[0].Columns.Contains("会员单位") || !uploadExcel.Tables[0].Columns.Contains("限额人数"))
					{
						hasError = true;
						ClientScript.RegisterClientScriptBlock(this.GetType(), "AlertMsg", "<script type='text/javascript'>alert('上传的文件格式不正确,请重新上传！');</script>");
					}
					else
					{
						var memberList = DAL.DALHelper.Instance.GetList<TB_Member>(p => true);
						var list = DAL.DALHelper.Instance.GetList<TB_ExamQuota>(p => p.ExamID == ExamID);
						string msg = string.Empty;
						foreach (DataRow row in uploadExcel.Tables[0].Rows)
						{
							var member = memberList.Where(p => row["会员单位"] != null && p.Name.Trim() == row["会员单位"].ToString().Trim()).FirstOrDefault();

							if (member != null)
							{
								int count = 0;
								int.TryParse(row["限额人数"].ToString(), out count);
								if ((row["限额人数"] != null && string.IsNullOrWhiteSpace(row["限额人数"].ToString())) || count == 0)
									continue;
								if (count > 0)
								{
									var exam = list.Where(p => p.MemberID == member.ID).FirstOrDefault();
									if (exam == null)
									{
										exam = DAL.DALHelper.Instance.Create<TB_ExamQuota>();
										exam.ExamID = ExamID;
										exam.MemberID = member.ID;
										exam.Count = count;
										DAL.DALHelper.Instance.Add(exam);
										list.Add(exam);
									}
									else
									{
										exam.Count = count;
									}
								}
								else
								{
									hasError = true;
									msg += "会员单位:" + row["会员单位"].ToString() + ",限额人数:" + row["限额人数"].ToString() + "\\r\\n";
								}
							}
							else
							{
								hasError = true;
								msg += "会员单位:" + row["会员单位"].ToString() + ",限额人数:" + row["限额人数"].ToString() + "\\r\\n";
							}
						}
						if (!string.IsNullOrWhiteSpace(msg))
						{
							hasError = true;
							ClientScript.RegisterClientScriptBlock(this.GetType(), "AlertMsg", "<script type='text/javascript'>alert(\"上传的文件内容不正确:\\r\\n" + msg + "有误,请重新上传！\");</script>");
						}
					}
				}
				catch (Exception ex)
				{
					hasError = true;
					ClientScript.RegisterClientScriptBlock(this.GetType(), "AlertMsg", "<script type='text/javascript'>alert('" + ex.Message + "');</script>");
				}
			}

			if (!hasError)
			{
				DAL.DALHelper.Instance.SaveChanges();
				RefreshNewsList();
			}
        }

        protected void EditSubbtn_Click(object sender, EventArgs e)
        {
			bool hasError = false;
            int Id = Convert.ToInt32(this.HidSubjectId.Value);

            TB_Examination resultE = DAL.DALHelper.Instance.GetObject<TB_Examination>("ID", Id);
            resultE.Subject = this.Subject.Text.ToString();
            resultE.StartDate = Convert.ToDateTime(this.StartDate.Text);
            resultE.EndDate = Convert.ToDateTime(this.EndDate.Text);
            resultE.Deadline = Convert.ToDateTime(this.ExamDate.Text);
            DAL.DALHelper.Instance.Update<TB_Examination>(resultE);

            //删除已有的单位限额
            Expression<Func<TB_ExamQuota, bool>> f = (p => p.ExamID == Id);
			DAL.DALHelper.Instance.DeleteWithTransaction<TB_ExamQuota>(f);

            string CompanyStr = this.StrCompany.Value;
            string[] Company = CompanyStr.Split('⊙');
            foreach (string C in Company)
            {
                if (C != "")
                {
                    string[] cnt = C.Split('-');
                    TB_ExamQuota NewObjCQ = DAL.DALHelper.Instance.Create<TB_ExamQuota>();
                    NewObjCQ.ExamID = Id;
                    NewObjCQ.MemberID = Convert.ToInt32(cnt[0]);
                    NewObjCQ.Count = Convert.ToInt32(cnt[1]);
                    DAL.DALHelper.Instance.Add<TB_ExamQuota>(NewObjCQ);
                }
			}
			DAL.DALHelper.Instance.SaveChanges();

			if(!string.IsNullOrWhiteSpace(file_Excel.FileName))
			{
				try
				{
					string Url = BasePage.UploadFile(SystemCode, this.file_Excel.FileName, this.file_Excel.FileBytes); 
					string sheel = "Sheet1";
					var uploadExcel = ExcelAccess.GetExcelRows(Server.MapPath(Url), sheel);
					if (uploadExcel.Tables[0].Columns.Count < 2 || !uploadExcel.Tables[0].Columns.Contains("会员单位") || !uploadExcel.Tables[0].Columns.Contains("限额人数"))
					{
						hasError = true;
						ClientScript.RegisterClientScriptBlock(this.GetType(),"AlertMsg","<script type='text/javascript'>alert('上传的文件格式不正确,请重新上传！');</script>");
					}
					else
					{
						var memberList = DAL.DALHelper.Instance.GetList<TB_Member>(p => true);
						var list = DAL.DALHelper.Instance.GetList<TB_ExamQuota>(p => p.ExamID == Id);
						string msg = string.Empty;
						foreach (DataRow row in uploadExcel.Tables[0].Rows)
						{
							var member = memberList.Where(p => row["会员单位"] != null && p.Name.Trim() == row["会员单位"].ToString().Trim()).FirstOrDefault();
							if (member != null)
							{
								int count = 0;
								int.TryParse(row["限额人数"].ToString(), out count);
								if ((row["限额人数"] != null && string.IsNullOrWhiteSpace(row["限额人数"].ToString())) || count == 0)
									continue;
								if (count > 0)
								{
									var exam = list.Where(p => p.MemberID == member.ID).FirstOrDefault();
									if (exam == null)
									{
										exam = DAL.DALHelper.Instance.Create<TB_ExamQuota>();
										exam.ExamID = Id;
										exam.MemberID = member.ID;
										exam.Count = count;
										DAL.DALHelper.Instance.Add(exam);
										list.Add(exam);
									}
									else
									{
										exam.Count = count;
									}
								}
								else
								{
									hasError = true;
									msg += "会员单位:" + row["会员单位"].ToString() + ",限额人数:" + row["限额人数"].ToString() + "\\r\\n";
								}
							}
							else
							{
								hasError = true;
								msg += "会员单位:" + row["会员单位"].ToString() + ",限额人数:" + row["限额人数"].ToString() + "\\r\\n";
							}
						}
						if (!string.IsNullOrWhiteSpace(msg))
						{
							hasError = true;
							ClientScript.RegisterClientScriptBlock(this.GetType(),"AlertMsg","<script type='text/javascript'>alert(\"上传的文件内容不正确:\\r\\n" + msg + "有误,请重新上传！\");</script>");
						}
					}
				}
				catch(Exception ex)
				{
					hasError = true;
					ClientScript.RegisterClientScriptBlock(this.GetType(),"AlertMsg","<script type='text/javascript'>alert('" + ex.Message + "');</script>");
				}
			}

			if(!hasError)
			{
				DAL.DALHelper.Instance.SaveChanges();
				RefreshNewsList();
			}
        }

        protected void EditExamSubject(int id)
        {
            using (var ctx = new SBACNEntities())
            {
                var result = ctx.TB_Examination.Where(c => (c.ID == id)).FirstOrDefault();
                if (result != null)
                {
                    this.Subject.Text = result.Subject;
                    this.StartDate.Text = result.StartDate.ToString("yyyy-MM-dd");
                    this.EndDate.Text = Convert.ToDateTime(result.EndDate).ToString("yyyy-MM-dd");
                    this.ExamDate.Text = result.Deadline.ToString("yyyy-MM-dd");
                }

                var resultQuota = (from E in ctx.TB_ExamQuota
                                   join M in ctx.TB_Member on E.MemberID equals M.ID
                                   where E.ExamID == id
                                   select new
                                     {
                                         CompanyName = M.Name,
                                         CompanyId = M.ID,
                                         Count = E.Count
                                     }).ToList();

                //var resultQuota = ctx.TB_ExamQuota.Include("TB_Member").Where(c => (c.ExamID == id)).ToList();

                this.CompanyList.DataSource = resultQuota;
                this.CompanyList.DataBind();
            }
        }

		private void RefreshNewsList()
		{
			string clientJs = "<script type=\"text/javascript\" language=\"javascript\">";
			clientJs += "if(window.opener && window.opener.RefreshNewsList) window.opener.RefreshNewsList();";
			clientJs += "window.close();</script>";
			Response.Write(clientJs);
			Response.End();
		}

		protected void lbtn_DownLoad_Click(object sender, EventArgs e)
		{
			var result = DAL.DALHelper.Instance.GetList<TB_Member>(p => true);

			string strTmpFileName = "~/Template/考试报名限额.xls";
			string strDownloadFileName = "~/Template/Down/考试报名限额" + DateTime.Now.Ticks + ".xls";
			File.Copy(Server.MapPath(strTmpFileName), Server.MapPath(strDownloadFileName), true);

			string strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strDownloadFileName) + ";Extended Properties='Excel 8.0;HDR=Yes'";

			using (OleDbConnection conn = new OleDbConnection(strExcelConn))
			{
				conn.Open();
				OleDbCommand excmd = new OleDbCommand();
				excmd.Connection = conn;
				foreach (var l in result)
				{
					string insertcmd = " INSERT INTO [Sheet1$] (会员单位,限额人数) VALUES ('" + l.Name + "','0')";
					excmd.CommandText = insertcmd;
					excmd.ExecuteNonQuery();
				}
				conn.Close();
			}
			Response.Redirect(strDownloadFileName);
		}
    }
}